<!DOCTYPE html>
<html>
<head>
<title>高性能MySQL</title>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<style type="text/css">
/* GitHub stylesheet for MarkdownPad (http://markdownpad.com) */
/* Author: Nicolas Hery - http://nicolashery.com */
/* Version: b13fe65ca28d2e568c6ed5d7f06581183df8f2ff */
/* Source: https://github.com/nicolahery/markdownpad-github */

/* RESET
=============================================================================*/

html, body, div, span, applet, object, iframe, h1, h2, h3, h4, h5, h6, p, blockquote, pre, a, abbr, acronym, address, big, cite, code, del, dfn, em, img, ins, kbd, q, s, samp, small, strike, strong, sub, sup, tt, var, b, u, i, center, dl, dt, dd, ol, ul, li, fieldset, form, label, legend, table, caption, tbody, tfoot, thead, tr, th, td, article, aside, canvas, details, embed, figure, figcaption, footer, header, hgroup, menu, nav, output, ruby, section, summary, time, mark, audio, video {
  margin: 0;
  padding: 0;
  border: 0;
}

/* BODY
=============================================================================*/

body {
  font-family: Helvetica, arial, freesans, clean, sans-serif;
  font-size: 14px;
  line-height: 1.6;
  color: #333;
  background-color: #fff;
  padding: 20px;
  max-width: 960px;
  margin: 0 auto;
}

body>*:first-child {
  margin-top: 0 !important;
}

body>*:last-child {
  margin-bottom: 0 !important;
}

/* BLOCKS
=============================================================================*/

p, blockquote, ul, ol, dl, table, pre {
  margin: 15px 0;
}

/* HEADERS
=============================================================================*/

h1, h2, h3, h4, h5, h6 {
  margin: 20px 0 10px;
  padding: 0;
  font-weight: bold;
  -webkit-font-smoothing: antialiased;
}

h1 tt, h1 code, h2 tt, h2 code, h3 tt, h3 code, h4 tt, h4 code, h5 tt, h5 code, h6 tt, h6 code {
  font-size: inherit;
}

h1 {
  font-size: 28px;
  color: #000;
}

h2 {
  font-size: 24px;
  border-bottom: 1px solid #ccc;
  color: #000;
}

h3 {
  font-size: 18px;
}

h4 {
  font-size: 16px;
}

h5 {
  font-size: 14px;
}

h6 {
  color: #777;
  font-size: 14px;
}

body>h2:first-child, body>h1:first-child, body>h1:first-child+h2, body>h3:first-child, body>h4:first-child, body>h5:first-child, body>h6:first-child {
  margin-top: 0;
  padding-top: 0;
}

a:first-child h1, a:first-child h2, a:first-child h3, a:first-child h4, a:first-child h5, a:first-child h6 {
  margin-top: 0;
  padding-top: 0;
}

h1+p, h2+p, h3+p, h4+p, h5+p, h6+p {
  margin-top: 10px;
}

/* LINKS
=============================================================================*/

a {
  color: #4183C4;
  text-decoration: none;
}

a:hover {
  text-decoration: underline;
}

/* LISTS
=============================================================================*/

ul, ol {
  padding-left: 30px;
}

ul li > :first-child, 
ol li > :first-child, 
ul li ul:first-of-type, 
ol li ol:first-of-type, 
ul li ol:first-of-type, 
ol li ul:first-of-type {
  margin-top: 0px;
}

ul ul, ul ol, ol ol, ol ul {
  margin-bottom: 0;
}

dl {
  padding: 0;
}

dl dt {
  font-size: 14px;
  font-weight: bold;
  font-style: italic;
  padding: 0;
  margin: 15px 0 5px;
}

dl dt:first-child {
  padding: 0;
}

dl dt>:first-child {
  margin-top: 0px;
}

dl dt>:last-child {
  margin-bottom: 0px;
}

dl dd {
  margin: 0 0 15px;
  padding: 0 15px;
}

dl dd>:first-child {
  margin-top: 0px;
}

dl dd>:last-child {
  margin-bottom: 0px;
}

/* CODE
=============================================================================*/

pre, code, tt {
  font-size: 12px;
  font-family: Consolas, "Liberation Mono", Courier, monospace;
}

code, tt {
  margin: 0 0px;
  padding: 0px 0px;
  white-space: nowrap;
  border: 1px solid #eaeaea;
  background-color: #f8f8f8;
  border-radius: 3px;
}

pre>code {
  margin: 0;
  padding: 0;
  white-space: pre;
  border: none;
  background: transparent;
}

pre {
  background-color: #f8f8f8;
  border: 1px solid #ccc;
  font-size: 13px;
  line-height: 19px;
  overflow: auto;
  padding: 6px 10px;
  border-radius: 3px;
}

pre code, pre tt {
  background-color: transparent;
  border: none;
}

kbd {
    -moz-border-bottom-colors: none;
    -moz-border-left-colors: none;
    -moz-border-right-colors: none;
    -moz-border-top-colors: none;
    background-color: #DDDDDD;
    background-image: linear-gradient(#F1F1F1, #DDDDDD);
    background-repeat: repeat-x;
    border-color: #DDDDDD #CCCCCC #CCCCCC #DDDDDD;
    border-image: none;
    border-radius: 2px 2px 2px 2px;
    border-style: solid;
    border-width: 1px;
    font-family: "Helvetica Neue",Helvetica,Arial,sans-serif;
    line-height: 10px;
    padding: 1px 4px;
}

/* QUOTES
=============================================================================*/

blockquote {
  border-left: 4px solid #DDD;
  padding: 0 15px;
  color: #777;
}

blockquote>:first-child {
  margin-top: 0px;
}

blockquote>:last-child {
  margin-bottom: 0px;
}

/* HORIZONTAL RULES
=============================================================================*/

hr {
  clear: both;
  margin: 15px 0;
  height: 0px;
  overflow: hidden;
  border: none;
  background: transparent;
  border-bottom: 4px solid #ddd;
  padding: 0;
}

/* TABLES
=============================================================================*/

table th {
  font-weight: bold;
}

table th, table td {
  border: 1px solid #ccc;
  padding: 6px 13px;
}

table tr {
  border-top: 1px solid #ccc;
  background-color: #fff;
}

table tr:nth-child(2n) {
  background-color: #f8f8f8;
}

/* IMAGES
=============================================================================*/

img {
  max-width: 100%
}
</style>
</head>
<body>
<h1>第一章 MySQL架构与历史</h1>
<h2>1.1 MySQL逻辑架构</h2>
<h3>1.1.1 连接管理与安全性</h3>
<h3>1.1.2 优化与执行</h3>
<h2>1.2 并发控制</h2>
<h3>1.2.1 读写锁</h3>
<h3>1.2.2 锁粒度</h3>
<h2>1.3 事务</h2>
<h3>1.3.1 隔离级别</h3>
<h3>1.3.2 死锁</h3>
<h3>1.3.3 事务日志</h3>
<h3>1.3.4 MySQL中的事务</h3>
<h2>1.4 多版本并发控制</h2>
<h2>1.5 MySQl的存储引擎</h2>
<h3>1.5.1 InnoDB存储引擎</h3>
<h3>1.5.2 MyISAM存储引擎</h3>
<h3>1.5.3 MySQL内建的其他存储引擎</h3>
<h3>1.5.4 第三方存储引擎</h3>
<h3>1.5.5 选择合适的引擎</h3>
<h3>1.5.6 转换表的引擎</h3>
<h2>1.6 MySQl时间线（Timeline）</h2>
<h2>1.7 MySQL的开发模式</h2>
<h2>1.8 总结</h2>
<h1>第二章 MySQL基准测试</h1>
<h2>2.1 为什么需要基准测试</h2>
<h2>2.2 基准测试的策略</h2>
<h3>2.2.1 测试何种指标</h3>
<h2>2.3 基准测试方法</h2>
<h3>2.3.1 设计和规划基准测试</h3>
<h3>2.3.2 基准测试该运行多长时间</h3>
<h3>2.3.3 获取系统性能和状态</h3>
<h3>2.3.4 获得准确的测试结果</h3>
<h3>2.3.5 运行基准测试并分析结果</h3>
<h3>2.3.6 绘图的重要性</h3>
<h2>2.4 基准测试工具</h2>
<h3>2.4.1 集成式测试工具</h3>
<h3>2.4.2 单组件式测试工具</h3>
<h2>2.5 基准测试案例</h2>
<h3>2.5.1 http_load</h3>
<h3>2.5.2 MySQL基准测试套件</h3>
<h3>2.5.3 sysbench</h3>
<h3>2.5.4 数据库测试套件中的dbt2 TPC-C测试</h3>
<h3>2.5.5 Percona的TPCC-MySQL测试工具</h3>
<h2>2.6 总结</h2>
<h1>第三章 服务器性能剖析</h1>
<h2>3.1 性能优化简介</h2>
<h3>3.1.1 通过性能剖析进行优化</h3>
<h3>3.1.2 理解性能剖析</h3>
<h2>3.2 对应用程序进行性能剖析</h2>
<h3>3.2.1 测量PHP应用程序</h3>
<h2>3.3 剖析MySQL查询</h2>
<h3>3.3.1 剖析服务器负载</h3>
<h3>3.3.2 剖析单条查询</h3>
<h3>3.3.3 使用性能剖析</h3>
<h2>3.4 诊断间歇性问题</h2>
<h3>3.4.1 单条查询问题还是服务器问题</h3>
<h3>3.4.2 捕获诊断数据</h3>
<h3>3.4.3 一个诊断案例</h3>
<h2>3.5 其他剖析工具</h2>
<h3>3.5.1 使用USER_STATISTICS表</h3>
<h3>3.5.2 使用strace</h3>
<h2>3.6 总结</h2>
<h1>第四章 Schema与数据类型优化</h1>
<p>根据系统将要执行的查询语句来设计Schema。</p>
<h2>4.1 选择优化的数据类型</h2>
<h4>更小的通常更好</h4>
<p>应尽量使用可以正确存储数据的最小数据类型。</p>
<p>确保没有低估需要存储的值得范围。（在Schema中的多个地方增加数据类型的范围是一个非常耗时和痛苦的工作）</p>
<h4>简单就好</h4>
<p>简单数据类型的操作通常需要更少的CPU周期。</p>
<p>用整型存储IP地址。</p>
<h4>尽量避免NULL</h4>
<p>可为NULL的列使得索引、索引统计和值比较都会变得复杂。</p>
<p>InnoDB使用单独的位（bit）存储NULL值，所以对稀疏数据有很好的空间效率。但是不适用于MyISAM。</p>
<p>TIMESTAMP只使用DATETIME一半的存储空间，并且会根据时区变化。但是允许的时间范围小。</p>
<h3>4.1.1 整数类型（TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT）</h3>
<p>整数计算一般使用64位的BIGINT整数，即使在32位环境也是如此。
MySQL可以为整数指定宽度，例如INT(11)（不会限制值的合法范围，只是规定了交互工具用来显示字符的个数）。</p>
<h3>4.1.2 实数类型</h3>
<p>定义：实数是带有小数部分的数字。但是不只是为了存储小数，也可以使用DECIMAL存储比BIGINT还大的整数。</p>
<ol>
<li>
<p>MySQL既支持精确类型，也支持不精确类型。</p>
</li>
<li>
<p>CPU支持原生浮点类型，不支持DECIMAL类型，所以原生浮点类型运算更快。</p>
</li>
<li>
<p>DECIMAL可以指定小数点前后允许的最大位数，例如DECIMAL(18,9)。（每4个字节存9个数字，小数点本身占一个字节）。</p>
</li>
<li>
<p>MYSQL5.0后DECIMAL允许最多65个数字。</p>
</li>
<li>
<p>在计算时DECIMAL会转换为DOUBLE类型。</p>
</li>
<li>
<p>应尽量只在对小数进行精确计算时才使用DECIMAL。</p>
</li>
</ol>
<h3>4.1.3 字符串类型</h3>
<h4>VARCHAR和CHAR类型</h4>
<h5>VARCHAR</h5>
<ol>
<li>如果MySQL表使用ROW_FORMAT=FIXED创建的话，每一行都会使用定长存储。</li>
<li>VARCHAR需要使用1或2个额外字节记录字符串的长度。（根据列的长度大于或小于255个字节区分）</li>
<li>InnDB可以把过长的VARCHAR存储为BLOB。</li>
</ol>
<h5>CHAR（空格进行填充）</h5>
<ol>
<li>当存储CHAR值时，MySQL会删除所有的末尾空格。</li>
<li>优点：定长的CHAR类型不容易产生碎片。</li>
<li>字符串长度定义不是字节数，是字符数，多字节字符集会需要更多的空间存储单个字符。</li>
<li>填充和截取空格的行为在不同的存储引擎都是一样的，因为这是在MySQL服务器层处理的。</li>
<li>BINARY和VARBINARY存储二进制字符串，二进制字符串存储的是字节码而不是字符。（\0进行填充，检索时不会去掉）</li>
</ol>
<h5>BLOB和TEXT类型</h5>
<ol>
<li>为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。</li>
<li>只对每个列最前max<em>sort</em>length字节而不是整个字符串做排序。</li>
</ol>
<p><strong>如果EXPLAIN执行计划的Extra列包含“Using temporary”，则说明这个查询使用了隐式临时表。</strong></p>
<h5>使用枚举(ENUM)代替字符串类型。</h5>
<ol>
<li>MySQL会在内部将每个值在列表中的位置保存为整数，并且在表的.frm文件中保存“数字-字符串”映射关系的“查找表”。</li>
<li>枚举字段是按照内部存储的整数而不是定义的字符串进行排序的。（按照需要的方式定义枚举列）</li>
<li>对于一系列未来会改变的字符串，使用枚举不是一个好主意。</li>
</ol>
<h3>4.1.4 日期和时间类型</h3>
<p>除了特殊行为之外，通常应该尽量使用TIMESTAMP,因为它比DATATIME空间效率更高。</p>
<h3>4.1.5 位数据类型</h3>
<h4>BIT</h4>
<p>数字上下文场景异常，谨慎使用BIT类型。</p>
<h4>SET</h4>
<p>SET数据类型在MySQL内部是以一系列打包的位的集合来表示的。
一个替代SET的方式是使用一个整数包装一系列的位。</p>
<h3>4.1.6 选择标识符（identifier）</h3>
<p>在比较操作室隐式类型转换可能导致很难发现的错误。</p>
<p>随机值导致缓存对所有类型的查询语句效果都很差。</p>
<p><strong>对象关系映射（orm）系统是一种常见的性能噩梦。</strong></p>
<h3>4.1.7 特殊类型数据</h3>
<p>ipv4：MySQL提供INET<em>ATON()和INET</em>NTOA()函数在点分法字符串和无符号整数之间转换。</p>
<h2>4.2 MySQl schema设计中的陷阱</h2>
<h4>太多的列</h4>
<p>MySQL存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价是非常高的。转换的代价依赖于列的数量。</p>
<h4>太多的关联</h4>
<h4>全能的枚举</h4>
<p>应该用整数作为外键关联到字典表或者查找表来查找具体值。（防止表结构的修改）</p>
<h4>变相的枚举</h4>
<p>如果每次只会出现一个值（Y或N），应使用枚举代替SET。</p>
<h4>非此发明（Not Invent Here）的NULL</h4>
<ol>
<li>处理NULL确实不容易，但有时会比他的替代方案更好。</li>
<li>MySQL会在索引中存储NULL值，Oracle不会。</li>
</ol>
<h2>4.3 范式和反范式</h2>
<h3>4.3.1 范式的优点和缺点</h3>
<p>优点：范式的表通常更小，可以更好地方在内存里，所以执行操作会更快。</p>
<p>缺点：通常需要关联。</p>
<h3>4.3.2 反范式的优点和缺点</h3>
<p>单独的表能使用更高效的索引策略。</p>
<h3>4.3.3 混用范式化和反范式化</h3>
<p>在不同的表中存储相同的特定列。</p>
<p>排序的需要。</p>
<h2>4.4 缓存表和汇总表</h2>
<h3>4.4.1 物化视图</h3>
<h3>4.4.2 计数器表</h3>
<h2>4.5 加快ALTER TABLE操作的速度</h2>
<h3>4.5.1 只修改.frm文件</h3>
<h3>4.5.2 快速创建MyISAM索引</h3>
<h2>4.6 总结</h2>
<h1>第五章 创建高性能索引</h1>
<p><strong>背景：不恰当使用索引，当数据量逐渐增大时，性能会急剧下降。</strong></p>
<h2>5.1 索引基础</h2>
<p>如果索引包含多个列，那么列的顺序也十分重要，因为MySql只能高效地使用索引的最左前缀列。
<strong>Tip：关于ORM框架：除非只是生成非常基本的查询（例如仅是根据主键查询），否则很难生成适合索引的查询。</strong></p>
<h3>索引的类型</h3>
<p>在MySQL中，索引是在引擎层而不是在服务器层实现的。（并不是所有的引擎都支持索引）
<strong>Tip：很多存储引擎都是用的B+Tree，即每一个叶子节点都包含指向下一个叶子节点的指针。</strong>
MyISAM索引通过数据的物理位置引用被索引的行；InnoDB则根据主键引用被索引的行（所以二级索引需要两次索引查找）。</p>
<h4>可以使用B-Tree索引的查询类型</h4>
<ol>
<li>全值匹配</li>
<li>匹配最左前缀</li>
<li>匹配列前缀</li>
<li>匹配范围值</li>
<li>精确匹配某一列并范围匹配另外一列</li>
<li>只访问索引的查询</li>
</ol>
<p><strong>Tip：如果不是按照索引的最左列开始查找，则无法使用索引。</strong></p>
<h5>不能跳过索引中的列。</h5>
<h5>如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。</h5>
<h2>5.2 索引的优点</h2>
<h2>5.3 高性能的索引策略</h2>
<h3>5.3.1 独立的列</h3>
<h3>5.3.2 前缀索引和索引选择性</h3>
<h3>5.3.3 多列索引</h3>
<h3>5.3.4 选择合适的索引列排序</h3>
<h3>5.3.5 聚簇索引</h3>
<p>聚簇索引并不是一种单独的索引类型，而是一种存储方式。
因为是存储引擎负责索引，所以不是所有的存储引擎都是聚簇索引。
InnoDB只聚集在同一个页面中的记录，所以包含相邻键值的页面可能会相距甚远。
基于聚簇索引的表在插入新行，或者在主键被更新需要移动行的时候，可能面临页分裂（'page split'）的问题，页分裂会导致表占用更多的磁盘空间。</p>
<h4>1）InnoDB和MyISAM的数据分布对比</h4>
<h5>MyISAM的数据分布</h5>
<p>按照数据插入顺序存储在磁盘上。</p>
<h5>InnoDB的数据分布</h5>
<p>聚簇索引就是表。
InnoDB二级索引存储主键值的好处？减少出现行移动或者数据页分列时二级索引的维护工作。
B-Tree的非叶子节点包含了索引列和指向下一级节点的指针。</p>
<h4>2) 在InnoDB中按主键顺序插入行</h4>
<p>如果正在使用InnoDB表并且没有数据需要聚集，可以定义一个代理键（surrogate key）作为主键。
example：AUTO_INCREMENT自增列</p>
<p>WARN:必须保证能够顺序写入。（不要使用UUID，会导致页分裂和碎片）</p>
<p>页的最大填充因子（InnoDB默认为15/16，留出部分空间用于以后的修改）</p>
<p>问题：OPTIMIZE TABLE的作用？
问题：顺序的主键什么时候会造成更坏的效果？</p>
<h3>5.3.6 覆盖索引</h3>
<p>原理：MySQL可以使用索引直接获取列的数据，不需要再读取数据行。如果索引的叶子节点已经包含要查询的数据，那么就是覆盖索引。</p>
<p>InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级节点能够覆盖查询，则可以避免对主键索引的二次查询。</p>
<p>覆盖索引必须要存储索引列的值，MySQL只能使用B-Tree索引做覆盖索引。（哈希索引、空间索引和全文索引都不支持存储索引列的值）。
不同存储引擎实现覆盖索引的方式不同，而且不是所有引擎搜支持覆盖索引。</p>
<p>通过EXPLAIN测试，其中Extra表明使用了覆盖索引。</p>
<h5>type列和Extra列的区别：type列表示查询访问数据的方式（join type），Extra列表示是否使用覆盖索引。</h5>
<p>```
mysql&gt; EXPLAIN SELECT store<em>id, film</em>id FROM sakila.inventory\G;
*************************** 1. row ***************************
           id: 1
  select<em>type: SIMPLE
        table: inventory
   partitions: NULL
         type: index
possible<em>keys: NULL
          key: idx</em>store</em>id<em>film</em>id
      key_len: 3
          ref: NULL
         rows: 4581
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)</p>
<p>ERROR:
No query specified
```
MySQL只能在索引中执行最左前缀匹配的LIKE比较查询，不能执行通配符开头的LIKE查询。</p>
<h5>延迟关联（deferred join）</h5>
<p>原理：在查询的第一阶段（子查询）覆盖索引。
索引：products(artist,title,prod<em>id)
<code>SELECT *
FROM products
  JOIN (
    SELECT prod_id
    FROM products
    WHERE actor='SEAN CARREY' AND title LIKE ‘%APOLLO%’
  ) AS t1 ON (t1.prod_id=products.prod_id) ;</code>
Tip：外查询可以利用其它索引。（已知prod</em>id）</p>
<p>问题： 索引条件推送（index condition pushdown）（可以非显式使用延迟关联）</p>
<h3>5.3.7 使用索引扫描来做排序</h3>
<p>背景：MySQL有两种方式可以生成有序的结果：通过排序操作；按索引顺序扫描。</p>
<p>1、如果索引不能覆盖所查找的全部的列，那么每扫描到一条索引记录都会回表查询一次对应的行（基本上是随机I/O）。
2、只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果做排序。
3、如果查询关联多张表，那么只有当ORDER BY子句引用的字段全部为第一张表时，才能用索引做排序。
4、索引中的列要满足最左前缀要求。（例外：前导列为常量）</p>
<h3>5.3.8 压缩（前缀压缩）索引（MyISAM）</h3>
<h4>背景</h4>
<p>MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引放入内存中。某些情况下可以极大地提高性能。默认只压缩字符串，但是通过参数设置也可以对整数做压缩。</p>
<h4>方法</h4>
<p>保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，保存这些数据。
example： perform（perform） performance（7,ance）
MyISAM对行指针也采用类似的前缀压缩方式。</p>
<h4>缺点</h4>
<p>无法进行二分查找，只能从头开始扫描。
倒序扫描速度慢，在块中查找某一行平均要扫描半个块。</p>
<h4>结论</h4>
<p>适用于I/O密集型应用，不适用于CPU密集型应用。</p>
<h3>5.3.9 冗余和重复索引</h3>
<p>背景：MySQl允许在相同列上创建多个索引。</p>
<p>重复索引是在相同列上按照相同的顺序创建的相同类型的索引。（发现后应立即移除）</p>
<h3>5.3.10 未使用的索引</h3>
<h3>5.3.11 索引和锁</h3>
<h2>5.4 索引案例学习</h2>
<h3>5.4.1 支持多种过滤条件</h3>
<h3>5.4.2 避免多个范围条件</h3>
<h3>5.4.3 优化排序</h3>
<h2>5.5 维护索引和表</h2>
<h3>5.5.1 找到并修复损坏的表</h3>
<h3>5.5.2 更新索引统计信息</h3>
<h3>5.5.3 减少索引和数据的碎片</h3>
<h2>5.6 总结</h2>
<h1>第六章 查询性能优化</h1>
<p>优化的方面：查询优化、索引优化、库表结构优化</p>
<h2>6.1 为什么查询速度会慢</h2>
<p>快速的查询真正重要的是<strong>响应时间</strong>。</p>
<p>查询的生命周期
1. 客户端
2. 服务器
3. 在服务器上解析
4. 生成执行计划
5. 执行（最重要的阶段）
6. 返回结果给客户端</p>
<h2>6.2 慢查询基础：优化数据访问</h2>
<p>查询性能低下最基本的原因是访问的数据太多
优化方式：减少访问的数据量</p>
<p>分析步骤：
1. 确认应用是否在检索大量超过需要的数据。
2. 确认MySQl服务器层是否在分析大量超过需要的数据行。</p>
<h3>6.2.1 是否向数据库请求了不需要的列</h3>
<p>有些查询会请求超过实际需要的数据，然后多余的数据会被应用程序丢弃。
1. 查询不需要的记录。（在这样的查询后面加上LIMIT）
2. 多表关联时返回全部列。
3. 总是取出全部列。
4. 重复查询相同的数据。（应使用缓存技术）</p>
<h3>6.2.2 MySQL是否在扫描额外的记录</h3>
<p>衡量开销的指标
-   响应时间
-   扫描的行数
-   返回的行数</p>
<p>这三个指标全部记录在慢查询日志中。
检查慢查询日志是找出扫描行数过多的查询的好办法。</p>
<h4>响应时间</h4>
<p>响应时间分为：服务时间和排队时间
影响响应时间的因素：存储引擎的锁（表锁、行锁）、高并发资源竞争、硬件响应等。</p>
<p>判断响应时间是否合理？
了解这个查询需要哪些索引以及执行计划，计算大概需要多少个顺序和随机I/O，乘以在具体硬件下I/O的消耗时间，最后加起来，得到参考值。</p>
<h4>扫描的行数和返回的行数</h4>
<p>扫描的行数
1. 分析查询时，查询扫描的行数可以说明该查询找到需要的数据的效率高不高。
2. 但是不是所有的行访问的代价都是相同的。</p>
<p>返回的行数
1. 扫描的行数对返回的行数的比例一般在1:1和10:1之间。（MySQL不会显示生成结果实际上需要扫描多少行数据，只会显示生成结果时一共扫描了多少行数据）</p>
<h4>扫描的行数和访问类型</h4>
<ol>
<li>在评估查询开销时，需要考虑从表中找到某一行的成本。</li>
<li>EXPLAIN语句中的type列反映了访问类型？
全表扫描、索引扫描、范围扫描、唯一索引查询、常数引用等（速度从快到慢）。</li>
<li>如果查询不能找到合适的访问类型，通常应增加一个合适的索引。</li>
</ol>
<h4>应用where条件的方式</h4>
<ol>
<li>在索引中使用WHERE条件来过滤不匹配的记录。（在存储引擎层完成）</li>
<li>使用索引覆盖扫描（在EXTRA列中出现了Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。（这是在MySQl服务器中完成的，但无须再回表查询）</li>
<li>从数据表中返回数据，然后过滤不满足的条件（在Extra列中出现Using where）。（在MySQl服务器中完成，需要先从数据表中读出记录然后过滤）</li>
</ol>
<p><strong>并不是说增加索引就能让扫描的行数等于返回的行数</strong></p>
<h4>扫描大量数据只返回少数的行的解决方法</h4>
<ol>
<li>使用覆盖索引扫描，把所有需要的列都放到索引中，这样存储引擎无须回表获取对应行就可以返回结果。</li>
<li>改变库表结构。（例如使用单独的汇总表）</li>
<li>重写这个复杂的查询。</li>
</ol>
<h2>6.3 重构查询的方式</h2>
<h3>6.3.1 一个复杂的查询还是多个简单的查询</h3>
<p>是否需要将一个复杂的查询分成多个简单的查询。
原因：MySQl从设计上让链接和断开都很轻量级，在返回一个小的查询结果方面很高效。</p>
<p>如果一个查询能够胜任时还写成多个独立查询是不明智的。</p>
<h3>6.3.2 切分查询</h3>
<ol>
<li>原理：“分而治之”，将大查询切分成小查询。</li>
<li>例子：定期清除大量数据时，如果用一个大的语句一次性完成，则可能需要一次锁住很多数据、占满整个事务日志、耗尽系统资源、阻塞很多小的但是重要的查询。</li>
<li>如果是事务型引擎，很多时候小事务能够更高效。</li>
</ol>
<h3>6.3.3 分解关联查询</h3>
<p>很多高性能的应用都会对关联查询进行分解，可以对每一个表进行一次单表查询，然后将结果在应用程序中进行关联。</p>
<p>分解关联查询的优势？
1. 让缓存的效率更高。
2. 将查询分解后，执行单个查询可以减少锁的竞争。
3. 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展。
4. 查询效率本身也会提升。（取出数据后再使用IN()操作比关联查询中的随机查询效率更高）
5. 减少冗余记录的查询。
6. 相当于在应用中实现了哈希关联，而不是用MySQL的嵌套循环关联。</p>
<p>关联查询的使用场景？
1. 当应用能够方便地缓存单个查询的结果的时候。
2. 当可以将数据拆分到不同的MySQL服务器上的时候。
3. 当能够使用IN()的方式代替关联查询的时候。
4. 当查询中使用同一个数据表的时候。</p>
<h2>6.4 执行查询的基础</h2>
<h3>6.4.1 MySQl客户端/服务器通信协议</h3>
<p>MySQL客户端和服务器之间的通信协议是“半双工”的。（不能进行流量控制）</p>
<p>接受全部结果并缓存通常可以减少服务器压力，让查询能够早点结束、早点释放相应的资源。</p>
<h4>查询状态</h4>
<p><code>SHOW FULL PROCESSLIST ;</code>
1. Sleep
2. Query
3. Locked
4. Analyzing and statistics
5. Copy to tmp table [on disk]
6. Sorting result
7. Sending data</p>
<h3>6.4.2 查询缓存</h3>
<p>在解析查询语句之前，如果查询缓存是打开的，优先查找缓存（通过大小写敏感的哈希查找实现），如果命中，检查用户权限（通过查询缓存中当前查询需要访问的表信息查看）</p>
<h3>6.4.3 查询优化处理</h3>
<p>将SQL转换成一个执行计划。
过程：解析SQL、预处理、优化SQL执行计划。
能够优化的类型？
1. 重新定义关联表顺序。
2. 将外连接转化为内连接。
3. 使用等价变化规则。
4. 优化COUNT()、MIN()、MAX()
5. 预估并转化为常数表达式。
6. 覆盖索引扫描。
7. 子查询优化。
8. 提前终止查询。
9. 等值传播。
10. 列表IN()的比较。</p>
<h3>6.4.4 查询执行引擎</h3>
<h3>6.4.5 返回结果给客户端</h3>
<h2>6.5 MySQL查询优化器的局限性</h2>
<h3>6.5.1 关联子查询</h3>
<h3>6.5.2 UNION的限制</h3>
<h3>6.5.3 索引合并优化</h3>
<h3>6.5.4 等值传递</h3>
<h3>6.5.5 并行执行</h3>
<h3>6.5.6 哈希关联</h3>
<h3>6.5.7 松散索引扫描</h3>
<h3>6.5.8 最大值和最小值优化</h3>
<h3>6.5.9 在同一个表上查询和更新</h3>
<h2>6.6 查询优化器的提示（hint）</h2>
<h2>6.7 优化特定类型的查询</h2>
<h3>6.7.1 优化COUNT()查询</h3>
<h3>6.7.2 优化关联查询</h3>
<h3>6.7.3 优化子查询</h3>
<h3>6.7.4 优化GROUP BY和DESTINCT</h3>
<h3>6.7.5 优化LIMIT分页</h3>
<h3>6.7.6 优化SQL<em>CALC</em>FOUND_ROWS</h3>
<h3>6.7.7 优化UNION查询</h3>
<h3>6.7.8 静态查询分析</h3>
<h3>6.7.9 使用用户自定义变量</h3>
<h2>6.8 案例学习</h2>
<h3>6.8.1 使用MySQL构建一个队列表</h3>
<h3>6.8.2 计算两点之间的距离</h3>
<h3>6.8.3 使用用户自定义函数</h3>
<h2>6.9 总结</h2>

</body>
</html>
<!-- This document was created with MarkdownPad, the Markdown editor for Windows (http://markdownpad.com) -->
